package sui.utils.excel;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import sui.utils.date.DateTimeUtils;

/**
 * <p>
 * Description: 读取Excel生成SQL(MySQL)
 * </p>
 * @author XQF SUI
 * @created 2017年4月21日 上午10:22:00
 * @version 1.0
 */
public class ReaderExcelGenerateSQL {

	/**
	 * 替换规则
	 */
	private static final String REGEX = "\\$\\{(.+?)\\}";

	/**
	 * 默认Excel sheet页坐标
	 */
	private static final Integer DAFALUT_EXCEL_SHEET_INDEX = 0;
	
	/**
	 * 默认标题行坐标
	 */
	private static final Integer DAFALUT_TITLE_ROW_INDEX = 0;
	
	/**
	 * 默认数据开始行坐标
	 */
	private static final Integer DAFALUT_DATA_ROW_INDEX = 1;
	
	private static List<String> olist = new ArrayList<>();
	
	static {
		olist.add("026722b92f43405680d392f2e7ac94fe");
		olist.add("0285519a498f461ab9d4aec670f5964d");
		olist.add("0417385cc80343a29f7b655f19e3c0c1");
		olist.add("0841924581dd46ffa4a6a2cfdf866d7b");
		olist.add("0dccdb8b827d40388386f60fc7e36c87");
		olist.add("112608cdc7d8426d85740057d88dfcb6");
		olist.add("11b506253eed4a87b40bec4fa1e00969");
		olist.add("1d5e320f626b42859258ceace132ab54");
		olist.add("23904fb68954466c85e531817d2987f8");
		olist.add("2a4dd2e105ed4771b48670369a31803f");
		olist.add("3e9183f47b4848d6827f73953edf3e50");
		olist.add("419ab04e9d0241639155c34669ae35ef");
		olist.add("4717455843ea4b8e90322841dc5a3e9f");
		olist.add("475f243b9bcb4938a658fd0392efd3a8");
		olist.add("48fb221a66844d3586c4ced1cb2c441c");
		olist.add("49d809246d6040caa50ebb0fc33d4c1c");
		olist.add("4b8b1043131f414d9f0a41970fa74b65");
		olist.add("4f45ccc1ea0c42eba40035e5d1a1b469");
		olist.add("5596c24c6abf486aa4f02ae9a4502599");
		olist.add("5be92a69e8cf41c094bb13dbeb0aade6");
		olist.add("5d9e434ab85c491599edf4be0c97cca9");
		olist.add("5e4264b531ff4d849ea7d8b7492dc34a");
		olist.add("60e55d12e7b54ab78394d54fc8289eff");
		olist.add("65096c541c3745bc9d88306f17061e0f");
		olist.add("65d120e0468c43f5873204725c55b012");
		olist.add("6802a6d3dc014cf2abb70330d5f6222a");
		olist.add("6bb950f48a64490ba028ff56dafb2fbe");
		olist.add("6f603a2d16a04e71870efbb5b8039450");
		olist.add("6f858d7a31414929a2a1f8eb0a0901ab");
		olist.add("723239ee6df7422d9b3e4d4316769bc0");
		olist.add("78edc9fc6107424298bb52588ee17e2c");
		olist.add("81cea9bd884545b79e7633f85a589c13");
		olist.add("88dce61cfd724896b9fdf698c986550c");
		olist.add("89f0bc95442e499abf8b2030f9743980");
		olist.add("8b96b6b6cc8b4adf9ede2b93dd199990");
		olist.add("9135a9d870554b0bbb0017dd2f532de9");
		olist.add("927c41b2dbf14ce5ac2147a8734ea97d");
		olist.add("96db815444e54b47bced47cad5625996");
		olist.add("9abdb9a3a6384234be4efd3c37e9a80a");
		olist.add("9d40710da325491c84d79c44c9d319f7");
		olist.add("E1515CE476E86F99E0430B3C960AFA45");
		olist.add("a050ab8dc5b245d79189833a8920eceb");
		olist.add("a08e4a5a1d444f549c96b66bdb29f41a");
		olist.add("a64d9b949d4c4da6bb4c9ea14716bc05");
		olist.add("a79342ff133a41f5b24f6e8f451744f2");
		olist.add("af50bb4494484a67bc2f44369cc6c79a");
		olist.add("b13f0f11427a448b9f55f3771531b891");
		olist.add("b4f9869de41b4e2b88c4d30e041a080f");
		olist.add("b7e2b956093b4fe5a0efca0b606cc6a3");
		olist.add("bb17c1c493a94d57b4a6e1c657967e7a");
		olist.add("bd6b44e776424a2a8802d42cb92d1eeb");
		olist.add("bd6f44d651624fb68e2e95407d720c9f");
		olist.add("c306e5fe11964a13921b3a753ee8e27c");
		olist.add("c8f3a342bd8e48bfb6e8477ae57fadb6");
		olist.add("cebfb73f5c904725a198850c694b167b");
		olist.add("d30f483f0fb647f29baf6deed5080f21");
		olist.add("dc1d8bf91fb047ebb5d519e3a7df7e02");
		olist.add("e713f71b0b9c46609ae9f30fe81309a6");
		olist.add("ec6a354a73e0486d99954bce5d7e76f9");
		olist.add("f189b007d1804927853e809acc843252");
		olist.add("f879afb77dfb49e7bced2c37620e73d5");
		olist.add("ff264f612a2a4a3dbe6d35d41a778c61");
	}
	
	public static void readerExcelGenerateSQL(String excelFilePath, String baseSql, String wirteSqlFilePath)
			throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		readerExcelGenerateSQL(excelFilePath, DAFALUT_EXCEL_SHEET_INDEX, DAFALUT_TITLE_ROW_INDEX,
				DAFALUT_DATA_ROW_INDEX, baseSql, wirteSqlFilePath);
	}
	
	public static void readerExcelGenerateSQL(String excelFilePath, Integer sheetIndex, Integer titleRowIndex,
			Integer dataRowIndex, String baseSql, String wirteSqlFilePath)
			throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		List<String> sqlList = readerExcel(excelFilePath, sheetIndex, titleRowIndex, dataRowIndex, baseSql);

		if (sheetIndex == null) {
			sheetIndex = DAFALUT_EXCEL_SHEET_INDEX;
		}
		
		if (titleRowIndex == null) {
			titleRowIndex = DAFALUT_TITLE_ROW_INDEX;
		}
		
		if (dataRowIndex == null) {
			dataRowIndex = DAFALUT_DATA_ROW_INDEX;
		}
		
		StringBuilder builder = new StringBuilder();

		for (String sql : sqlList) {
			builder.append(sql).append(" \r\n");
			System.out.println(sql);
		}

		wirteToFile(wirteSqlFilePath, readerTableName(baseSql), builder.toString());
	}
	
	private static String readerTableName(String baseSql) {
		
		if (StringUtils.isBlank(baseSql)) {
			return null;
		}
		
		String sql = baseSql.toLowerCase();
		
		if (sql.toLowerCase().startsWith("insert")) {
			return sql.substring(sql.indexOf("into") + 4, sql.indexOf("(") - 1).trim();
		} else if (sql.toLowerCase().startsWith("update")) {
			return sql.substring(sql.indexOf("update") + 6, sql.indexOf("set") - 1).trim();
		} else if (sql.toLowerCase().startsWith("delete")) {
			return sql.substring(sql.indexOf("from") + 4, sql.indexOf("where") - 1).trim();
		}
		
		return null;
	}

	/**
	 * <p>
	 * Desc: 读取Excel
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:16
	 * @return
	 * @throws EncryptedDocumentException
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	private static List<String> readerExcel(String filePath, Integer sheetIndex, Integer titleRowIndex, Integer dataRowIndex, String baseSql) throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		Sheet sheet = WorkbookFactory.create(new File(filePath)).getSheetAt(sheetIndex);
		
		Map<String, Integer> titleMap = readerTitle(sheet, titleRowIndex, new HashMap<String, Integer>()); 
		
		List<String> sqlList = new ArrayList<String>();
		
		if (titleMap == null || titleMap.isEmpty()) {
			return sqlList;
		}
		
		int i = 0;
		Set<Entry<String, Integer>> set = titleMap.entrySet();
		
		for (Row row : sheet) {
			if (i++ >= dataRowIndex) {
				Map<String, String> paramMap = new HashMap<String, String>();
				
				if (!olist.contains(ExcelUtils.getCellValues(row.getCell(2)).toString())) {
					for (Entry<String, Integer> entry : set) {
						paramMap = getCellValueToSQLValue(paramMap, entry.getKey(), row.getCell(entry.getValue()));
					}
					
					sqlList.add(replaceAll(new String(baseSql), paramMap));
				} 
			}
		}
		
		return sqlList;
	}

	/**
	 * <p>
	 * Desc: 获取Cell值并转换为SQL数据
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:32
	 * @param paramMap
	 * @param key
	 * @param cell
	 * @return
	 */
	public static Map<String, String> getCellValueToSQLValue(Map<String, String> paramMap, String key, Cell cell) {
		
		String val = "";
		
		if (cell == null) {
			paramMap.put(key, val);
			return paramMap;
		}
		
		switch (cell.getCellTypeEnum()) {
		case STRING:
			if ("null".equals(dataFilter(cell.getStringCellValue()))) {
				val = dataFilter(cell.getStringCellValue());
			} else {
				val = '\'' + dataFilter(cell.getStringCellValue()) + '\'';
			}
			
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				val = '\'' + DateTimeUtils.getTimeNormalString(cell.getDateCellValue()) + '\'';
			} else {
				val = String.valueOf(cell.getNumericCellValue());
				
				DecimalFormat df = new DecimalFormat("0");  
				  
				val = df.format(cell.getNumericCellValue());
				
				// ================== 定制化 ==================
				if ("-10".equals(val)) {
					val = "null";
				}
				
//				if (Pattern.compile("^[0-9]+.[0]+$").matcher(val).matches()) {
//					val  = val.substring(0, val.lastIndexOf("."));
//				}
				// ================== 定制化 ==================
				
				if (val.length() > 11) {
					val  = '\'' + val + '\'';
				}
			}
			
			break;
		case BOOLEAN:
			val = String.valueOf(cell.getBooleanCellValue());
			break;
		case FORMULA:
			// TODO 暂不处理
			break;
		case BLANK:
			break;
		default:
			break;
		}
		
		paramMap.put(key, val);
		
		return paramMap;
	}
	
	public static String dataFilter(String data) {
		return data.trim();
	}
	
	/**
	 * <p>
	 * Desc: 根据SQL模板生成SQL
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:59
	 * @param template
	 * @param data
	 * @return
	 */
	public static String replaceAll(String template, Map<String, String> data) {
		Pattern pattern = Pattern.compile(REGEX);
		Matcher matcher = pattern.matcher(template);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			String key = matcher.group(1);
			String value = data.get(key);
			if (StringUtils.isBlank(value)) {
				value = "";
				matcher.appendReplacement(sb, value);
				break;
			}
			value = value.replaceAll("\\$", "\\\\\\$");
			matcher.appendReplacement(sb, value);
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	/**
	 * <p>
	 * Desc: 读取标题(KEY)
	 * <p>
	 * @author XQF SUI
	 * @param titleRowIndex 
	 * @param sheet 
	 * @param map 
	 * @return 
	 * @created 2017年4月21日 上午10:41:13
	 */
	private static Map<String, Integer> readerTitle(Sheet sheet, Integer titleRowIndex, Map<String, Integer> map) {
		
		if (map == null) {
			map = new HashMap<String, Integer>();
		}
		
		int i = 0;
		
		for (Cell cell : sheet.getRow(titleRowIndex)) {
			map.put(getStringCellValue(cell), i++);
		}
		
		return map;
	}
	
	/**
	 * <p>
	 * Desc: 获取String类型的Cell值
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:06:38
	 * @param cell
	 * @return
	 */
	private static String getStringCellValue(Cell cell) {
		String strCell = null;
		
		switch (cell.getCellTypeEnum()) {
		case STRING:
			strCell = cell.getStringCellValue();
			break;
		case NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK:
			break;
		default:
			break;
		}
		
		return strCell;
	}
	
	/**
	 * <p>
	 * Desc: 写SQL文件
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:07:01
	 * @param data
	 * @param data 
	 * @throws IOException
	 */
	private static void wirteToFile(String sqlFilePath, String tableName, String data) throws IOException {
		
		String filePath = sqlFilePath + "generate_sql_" + DateTimeUtils.getNowTimeShortString() + '_' + tableName + '_' + (int) (Math.random() * ((999999 - 1) + 1)) + ".sql";
		
		File sqlFile = new File(filePath);
		
		if (!sqlFile.exists()) {
			sqlFile.createNewFile();
		}
		
		FileWriter fileWritter = new FileWriter(sqlFile, true);
		PrintWriter printWriter = new PrintWriter(fileWritter);
		printWriter.println(data);
		fileWritter.flush();
		printWriter.close();
		fileWritter.close();
        
        System.out.println("写入文件[ " + filePath + " ]完成!");
	}
}
